package com.disputemanager.db;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.log4j.Logger;

import com.disputemanager.beans.CaseHistoryBean;
import com.disputemanager.beans.CaseLogBean;
import com.disputemanager.beans.Dispute;
import com.disputemanager.config.Constants;
import com.disputemanager.utility.FileUtility;
import com.disputemanager.utils.DateUtil;

public class DBHandler {
	
	private static Logger LOGGER = Logger.getLogger(DBHandler.class);
	
	//SQL SERVER SETTINGS
	/*private final String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=Detail";
	private final String user = "sa";
	private final String password = "password-1";
	
	private final String GET_DISPUTE_DETAIL = "select * from Disputes nolock where disputeId=?";
	private final String GET_DISPUTES = "select * from Disputes nolock where merchantId=? and prodCode=? and disputeDesc=? and status='O'";
	private final String GET_CASELOG = "select * from CaseLog nolock where disputeId=?";
	private final String GET_CASE_HISTORY = "SELECT * FROM CASEHISTORY nolock where disputeId=?";
	//private final String GET_PASSWORD = "select password from Users where userId=?";
	private final String GET_MERCHANTID = "select merchantId from Users nolock where userId = ? and password=?";
	private final String GET_DISPUTE_COUNT_BY_PRODCODE = "select disputeDesc, prodCode, COUNT(disputeId) CCount from Disputes where merchantId=? and status='O' group by prodCode, disputeDesc";
	private final String GET_DSPUT_AMT = "select disputeAmt from Disputes nolock where disputeId=?";
	
	private final String UPDATE_DISPUTE_STATUS = "update Disputes set status=? where disputeId=?";
	
	private final String INSERT_CASELOG = "insert into CaseLog values (?,?,?,?,?,?)";
	private final String INSERT_CASEHISTORY = "insert into CaseHistory values (?,?,?,?)";
	private final String INSERT_NOTE = "insert into Note values (?, ?)";*/
	
	
	//HSQLDB SETTINGS
	private final String hsqlUrl = "jdbc:hsqldb:hsql://localhost:9001/";
	private final String hsqlUser = "sa";
	private final String hsqlPassword = "";
	
	private final String GET_DISPUTE_DETAIL = "select * from Disputes nolock where disputeId=?";
	private final String GET_DISPUTES = "select * from Disputes nolock where merchantId=? and prodCode=? and disputeDesc=? and status='O'";
	private final String GET_CASELOG = "select * from CaseLog nolock where disputeId=?";
	private final String GET_CASE_HISTORY = "SELECT * FROM CASEHISTORY nolock where disputeId=?";
	private final String GET_MERCHANTID = "select merchantId from Users nolock where userId = ? and password=?";
	private final String GET_DISPUTE_COUNT_BY_PRODCODE = "select disputeDesc, prodCode, COUNT(disputeId) CCount from Disputes where merchantId=? and status='O' group by prodCode, disputeDesc";
	private final String GET_DSPUT_AMT = "select disputeAmt from Disputes nolock where disputeId=?";
	
	private final String UPDATE_DISPUTE_STATUS = "update Disputes set status=? where disputeId=?";
	
	private final String INSERT_CASELOG = "insert into CaseLog(\"DISPUTEID\", \"ACTIONTIMESTAMP\", \"ACTIONDESC\", \"AMOUNT\", \"FROMUSER\", \"MESSAGE\") values (?,?,?,?,?,?)";
	private final String INSERT_CASEHISTORY = "insert into CaseHistory(\"DISPUTEID\", \"INSERTTIMESTAMP\", \"REFERDOCPATH\", \"DOCUMENTDESC\") values (?,?,?,?)";
	private final String INSERT_NOTE = "INSERT INTO NOTE(\"DISPUTEID\", \"NOTE\" ) VALUES ( ?, ?)";
	
	
	private Connection getConnection() throws ClassNotFoundException, SQLException{
		
		Connection newConnection = null;
		
		//Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		Class.forName("org.hsqldb.jdbc.JDBCDriver" );
		
		if(newConnection == null || newConnection.isClosed()){
			//newConnection = DriverManager.getConnection(url, user, password);
			newConnection = DriverManager.getConnection(hsqlUrl, hsqlUser, hsqlPassword);
		}
		
		return newConnection;
		
	}
	
	public int login(String username, String password) throws ClassNotFoundException, SQLException{
		
		Connection connection = getConnection();
		if(connection != null){
			PreparedStatement statement = connection.prepareStatement(GET_MERCHANTID);
			statement.setString(1, username);
			statement.setString(2, password);
			
			ResultSet result = statement.executeQuery();
			result.next();
			int merchantId = result.getInt(1);
			result.close();
			statement.close();
			connection.close();
			
			return merchantId;
		}
		
		return 0;
	}
	
	public ResultSet getDisputeCountsByProdCode(String merchantId) throws ClassNotFoundException, SQLException{
		
		ResultSet result = null;
		
		Connection connection = getConnection();
		if(connection != null){
			PreparedStatement statement = connection.prepareStatement(GET_DISPUTE_COUNT_BY_PRODCODE);
			statement.setString(1, merchantId);
			result = statement.executeQuery();
		}
		
		
		return result;
	}
	
	public Dispute getDisputeDetail(int disputeId) throws ClassNotFoundException, SQLException{
		
		Dispute disputeBean = new Dispute();
		
		Connection connection = getConnection();
		if(connection != null){
			PreparedStatement statement = connection.prepareStatement(GET_DISPUTE_DETAIL);
			statement.setInt(1, disputeId);
			
			ResultSet rs = statement.executeQuery();
			while(rs.next()){
				disputeBean.setDisputeId(disputeId);
				disputeBean.setDisputeAmt(rs.getFloat("disputeAmt"));
				disputeBean.setDisputeDesc(rs.getString("disputeDesc"));
				disputeBean.setMerchantId(rs.getInt("merchantId"));
				disputeBean.setProdCode(rs.getInt("prodCode"));
				if(rs.getString("status").equals(Constants.DisputeStatusOpen))
					disputeBean.setStatus(Constants.DisputeStatusOpenDesc);
				else if(rs.getString("status").equals(Constants.DisputeStatusWorking))
					disputeBean.setStatus(Constants.DisputeStatusWorkingDesc);
				else if(rs.getString("status").equals(Constants.DisputeStatusResolve))
					disputeBean.setStatus(Constants.DisputeStatusResolveDesc);
				disputeBean.setCHN(rs.getString("cardHolderNumber"));
				disputeBean.setCardType(rs.getString("cardType"));
				disputeBean.setJurisdiction(rs.getString("jurisdiction"));
				disputeBean.setTranId(rs.getString("transactionId"));
				disputeBean.setTranDate(DateUtil.getDateFromTimestamp(rs.getTimestamp("tranDate")));
				disputeBean.setTranAmount(String.valueOf(rs.getFloat("tranAmount")));
				disputeBean.setReasonCode(rs.getString("reasonCode"));
				disputeBean.setDueDate(DateUtil.getDateFromTimestamp(rs.getTimestamp("dueDate")));
				disputeBean.setCCN(rs.getString("ccn"));
			}
			
			rs.close();
			statement.close();
			connection.close();
		}
		
		return disputeBean;
		
	}
	
	public List<Dispute> getAllDisputeDetails(String merchantId, String prodCode, String disputeType) throws ClassNotFoundException, SQLException{
		
		Dispute disputeBean = null;
		List<Dispute> disputes = new ArrayList<Dispute>();
		
		Connection connection = getConnection();
		if(connection != null){
			PreparedStatement statement = connection.prepareStatement(GET_DISPUTES);
			statement.setString(1, merchantId);
			statement.setString(2, prodCode);
			statement.setString(3, disputeType);
			
			ResultSet rs = statement.executeQuery();
			while(rs.next()){
				disputeBean = new Dispute();
				disputeBean.setDisputeId(rs.getInt("disputeId"));
				disputeBean.setDisputeAmt(rs.getFloat("disputeAmt"));
				disputeBean.setDisputeDesc(rs.getString("disputeDesc"));
				disputeBean.setMerchantId(rs.getInt("merchantId"));
				disputeBean.setProdCode(rs.getInt("prodCode"));
				if(rs.getString("status").equals(Constants.DisputeStatusOpen))
					disputeBean.setStatus(Constants.DisputeStatusOpenDesc);
				else if(rs.getString("status").equals(Constants.DisputeStatusWorking))
					disputeBean.setStatus(Constants.DisputeStatusWorkingDesc);
				else if(rs.getString("status").equals(Constants.DisputeStatusResolve))
					disputeBean.setStatus(Constants.DisputeStatusResolveDesc);
				disputeBean.setCHN(rs.getString("cardHolderNumber"));
				disputeBean.setCardType(rs.getString("cardType"));
				disputeBean.setJurisdiction(rs.getString("jurisdiction"));
				disputeBean.setTranId(rs.getString("transactionId"));
				disputeBean.setTranDate(DateUtil.getDateFromTimestamp(rs.getTimestamp("tranDate")));
				disputeBean.setTranAmount(String.valueOf(rs.getFloat("tranAmount")));
				disputeBean.setReasonCode(rs.getString("reasonCode"));
				disputeBean.setDueDate(DateUtil.getDateFromTimestamp(rs.getTimestamp("dueDate")));
				disputeBean.setCCN(rs.getString("ccn"));
				
				disputes.add(disputeBean);
			}
			
			rs.close();
			statement.close();
			connection.close();
		}
		
		return disputes;
	}
	
	public boolean changeDisputeStatus(String disputeId, String merchantId, String action){
		
		boolean success = false;
		
		Connection connection = null;
		try{
			connection = getConnection();
			connection.setAutoCommit(false);
			LOGGER.debug("AutoCommit Set To FALSE");
			
			PreparedStatement updateStmt = connection.prepareStatement(UPDATE_DISPUTE_STATUS);
			if(action.equals(Constants.CaseLogActionAccept) || action.equals(Constants.CaseLogActionReject))
				updateStmt.setString(1, Constants.DisputeStatusResolve);
			else if(action.equals(Constants.CaseLogActionNote))
				updateStmt.setString(1, Constants.DisputeStatusWorking);
			else
				updateStmt.setString(1, Constants.DisputeStatusOpen);
			updateStmt.setString(2, disputeId);
			updateStmt.executeUpdate();
			int disputeNo = Integer.valueOf(disputeId);
			doCaseLog(connection, 
					new CaseLogBean(0, disputeNo, null, action, getDisputeAmount(connection,disputeNo), merchantId, "Updating Status."));
			
			connection.commit();
			success = true;
		}catch(Exception e){
			try {
				connection.rollback();
			} catch (SQLException e1) {
				LOGGER.error("Error while RollBack." , e1);
			}
			LOGGER.error("Error in changeDisputeStatus",e);
		}finally{
			if(connection != null){
				try {
					connection.setAutoCommit(true);
					LOGGER.debug("AutoCommit Set To TRUE");
					connection.close();
				} catch (SQLException e) {
					LOGGER.error("Error while Closing Connection.", e);
				}
			}
		}
		
		return success;
	}
	
	public List<CaseLogBean> getCaseLog(int disputeId) throws ClassNotFoundException, SQLException{
		
		CaseLogBean bean = null;
		List<CaseLogBean> caseLog = new ArrayList<CaseLogBean>();
		
		Connection connection = getConnection();
		if(connection != null){
			PreparedStatement statement = connection.prepareStatement(GET_CASELOG);
			statement.setInt(1, disputeId);
			
			ResultSet rs = statement.executeQuery();
			while(rs.next()){
				bean = new CaseLogBean();
				bean.setUid(rs.getInt("uid"));
				bean.setDisputeId(rs.getInt("disputeId"));
				bean.setActionTimeStamp(DateUtil.getDateFromTimestamp(rs.getTimestamp("actionTimeStamp")));
				bean.setActionDesc(rs.getString("actionDesc"));
				bean.setAmount(rs.getString("amount"));
				bean.setFrom(rs.getString("fromUser"));
				bean.setMessage(rs.getString("message"));
				
				caseLog.add(bean);
			}
			
			rs.close();
			statement.close();
			connection.close();
		}
		
		
		return caseLog;
	}
	
	public List<CaseHistoryBean> getCaseHistory(int disputeId) throws ClassNotFoundException, SQLException{
		
		CaseHistoryBean bean = null;
		List<CaseHistoryBean> caseHistory = new ArrayList<CaseHistoryBean>();
		
		Connection connection = getConnection();
		if(connection != null){
			PreparedStatement statement = connection.prepareStatement(GET_CASE_HISTORY);
			statement.setInt(1, disputeId);
			
			ResultSet rs = statement.executeQuery();
			while(rs.next()){
				bean = new CaseHistoryBean();
				bean.setUid(rs.getInt("uid"));
				bean.setDisputeId(rs.getInt("disputeId"));
				bean.setInsertTimeStamp(DateUtil.getDateFromTimestamp(rs.getTimestamp("insertTimeStamp")));
				bean.setDocumentDesc(rs.getString("documentDesc"));
				bean.setReferDocPath(rs.getString("referDocPath"));
				
				caseHistory.add(bean);
			}
			
			rs.close();
			statement.close();
			connection.close();
		}
		
		
		return caseHistory;
	}
	
	public boolean doDocumentUpload(String disputeId,String merchantId, InputStream in, String fileName){
		
		boolean success = false;		
		Connection connection = null;
		
		try{
			connection = getConnection();
			connection.setAutoCommit(false);
			LOGGER.debug("AutoCommit Set To FALSE");
			
			FileUtility.upload(in, fileName);
			
			doCaseHistory(connection, disputeId, fileName, Constants.DocumentTypeMSUP);
			int disputeNo = Integer.valueOf(disputeId);
			doCaseLog(connection,
					new CaseLogBean(0, disputeNo, null, Constants.CaseLogActionReject, getDisputeAmount(connection,disputeNo), merchantId, "Document uploaded as justification."));
			
			connection.commit();
			success = true;			
			
		}catch(Exception e){
			try {
				connection.rollback();
			} catch (SQLException e1) {
				LOGGER.error("Error while RollBack." , e1);
			}
			LOGGER.error(e);
		}finally{
			if(connection != null){
				try {
					connection.setAutoCommit(true);
					LOGGER.debug("AutoCommit Set To TRUE");
					connection.close();
				} catch (SQLException e) {
					LOGGER.error("Error while Closing Connection.", e);
				}
			}
		}
		
		return success;
	}
	
	public boolean attachNote(String disputeId,String merchantId, String note){
		
		Connection connection = null;
		boolean success = false;
		
		try{
			connection = getConnection();
			connection.setAutoCommit(false);
			
			PreparedStatement statement = connection.prepareStatement(INSERT_NOTE);
			statement.setInt(1, Integer.parseInt(disputeId));
			statement.setString(2, note);
			statement.executeUpdate();
			
			int disputeNo = Integer.valueOf(disputeId);
			doCaseLog(connection,
					new CaseLogBean(0, disputeNo, null, Constants.CaseLogActionNote, getDisputeAmount(connection,disputeNo), merchantId, "User attached an note for review."));
			connection.commit();
			success = true;
		}catch(Exception e){
			try {
				connection.rollback();
			} catch (SQLException e1) {
				LOGGER.error("Error while RollBack.", e1);
			}
		}finally{
			if(connection != null){
				try{
					connection.setAutoCommit(true);
					LOGGER.debug("AutoCommit Set To TRUE");
					connection.close();
				}catch(SQLException e){
					LOGGER.error("Error while Closing Connection.", e);
				}
			}
		}
		
		return success;
	}
	
	private void doCaseLog(Connection connection, CaseLogBean bean) throws NumberFormatException, SQLException{
		PreparedStatement insertStmt = connection.prepareStatement(INSERT_CASELOG);
		insertStmt.setInt(1, bean.getDisputeId());
		Timestamp t = new Timestamp(Calendar.getInstance().getTimeInMillis());
		insertStmt.setTimestamp(2, t);
		insertStmt.setString(3, bean.getActionDesc());
		insertStmt.setString(4, bean.getAmount());
		insertStmt.setString(5, bean.getFrom());
		insertStmt.setString(6, bean.getMessage());
		insertStmt.executeUpdate();
		
		LOGGER.info("Case Logged ->"+bean.toString());
	}
	
	private void doCaseHistory(Connection connection, String disputeId, String path, String action) throws NumberFormatException, SQLException{
		PreparedStatement insertStmt = connection.prepareStatement(INSERT_CASEHISTORY);
		insertStmt.setInt(1, Integer.parseInt(disputeId));
		Timestamp t = new Timestamp(Calendar.getInstance().getTimeInMillis());
		insertStmt.setTimestamp(2, t);
		insertStmt.setString(3, path);
		insertStmt.setString(4, action);
		insertStmt.executeUpdate();
		
		LOGGER.info("Case Hitory created with ->["+disputeId+","+path+","+action+"]");
	}
	
	private String getDisputeAmount(Connection con,int disputeId){
		
		String disputeAmt = "";
		try {
			PreparedStatement stmt = con.prepareStatement(GET_DSPUT_AMT);
			stmt.setInt(1, disputeId);
			ResultSet result = stmt.executeQuery();
			result.next();
			disputeAmt = String.valueOf(result.getFloat(1));
			result.close();
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Error getting dispute amount.", e);
		}
		
		return disputeAmt;
	}

}
